using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

// SMO namespaces
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer;

using SQLAnalyzer.Framework;

namespace SQLAnalyzer.Modules
{
    public partial class TablesForm : Form, IAnalyzerModule
    {
        private Server currentServer;

        public TablesForm()
        {
            InitializeComponent();
        }


        #region IAnalyzerModule Members

        void IAnalyzerModule.Initialize(Server activeServer)
        {
            currentServer = activeServer;
        }

        Form IAnalyzerModule.MdiParent
        {
            set 
            {
                this.MdiParent = value;
            }
        }

        void IAnalyzerModule.Show()
        {
            this.Show();
        }
        #endregion

        private void Tables_Load(object sender, EventArgs e)
        {
            ShowDatabases(false);
        }

        private void cboDatabase_SelectedIndexChanged(object sender, EventArgs e)
        {
            ShowTables();
        }

        private void lstTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            ShowColumns();
        }

        private void btnSchema_Click(object sender, EventArgs e)
        {
            GenerateXSD();
        }

        public void ShowDatabases(bool selectDefault)
        {
            // Show the current databases on the server
            Cursor csr = null;

            try
            {
                csr = this.Cursor;   // Save the old cursor
                this.Cursor = Cursors.WaitCursor;   // Display the waiting cursor

                // Clear control
                cboDatabase.Items.Clear();

                // Limit the database properties returned to just those that we use
                currentServer.SetDefaultInitFields(typeof(Database),
                    new String[] { "Name" });

                // Limit the table properties returned to just those that we use
                currentServer.SetDefaultInitFields(typeof(Table),
                    new String[] { "Name", "CreateDate", "IsSystemObject" });

                // Limit the column properties returned to just those that we use
                currentServer.SetDefaultInitFields(typeof(Column), new
                    String[] {"Name", "DataType", "SystemType", "Length", 
                        "NumericPrecision", "NumericScale", 
                        "XmlSchemaNamespace", "XmlSchemaNamespaceSchema", 
                        "DataTypeSchema", "Nullable", "InPrimaryKey"
                });

                // Add database objects to combobox; the default ToString will display the database name
                foreach (Database db in currentServer.Databases)
                {
                    if (!db.IsSystemObject && db.IsAccessible)
                    {
                        cboDatabase.Items.Add(db);
                    }
                }

                if ((selectDefault == true) &&
                    (cboDatabase.Items.Count > 0))
                {
                    cboDatabase.SelectedIndex = 0;
                }
            }
            catch (SmoException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                this.Cursor = csr;  // Restore the original cursor
            }
        }

        private void ShowTables()
        {
            Database db;
            Cursor csr = null;

            try
            {
                csr = this.Cursor;   // Save the old cursor
                this.Cursor = Cursors.WaitCursor;   // Display the waiting cursor

                // Clear the tables list
                lstTable.Items.Clear();

                // Show the current tables for the selected database
                if (cboDatabase.SelectedIndex >= 0)
                {
                    db = (Database)cboDatabase.SelectedItem;

                    // Prefetch the tables information
                    db.PrefetchObjects(typeof(Table));

                    foreach (Table tbl in db.Tables)
                    {
                        if (tbl.IsSystemObject == false)
                        {
                            lstTable.Items.Add(tbl);
                        }
                    }

                    // Select the first item in the list
                    if (lstTable.Items.Count > 0)
                    {
                        lstTable.SelectedIndex = 0;
                    }
                    else
                    {
                        // Clear the table detail list
                        //ColumnsListView.Items.Clear();
                    }
                }
            }
            catch (SmoException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                this.Cursor = csr;  // Restore the original cursor
            }
        }

        private void ShowColumns()
        {
            Cursor csr = null;
            ListViewItem ColumnListViewItem;
            Table tbl;

            try
            {
                csr = this.Cursor;   // Save the old cursor
                this.Cursor = Cursors.WaitCursor;   // Display the waiting cursor

                // Delay rendering until after list filled
                ColumnsListView.BeginUpdate();

                // Clear the columns list
                ColumnsListView.Items.Clear();

                // Show the current columns for the selected table
                if (lstTable.SelectedIndex >= 0)
                {
                    // Get the selected table object
                    tbl = (Table)lstTable.SelectedItem;

                    // Iterate through all the columns to fill list
                    foreach (Column col in tbl.Columns)
                    {
                        ColumnListViewItem =
                            ColumnsListView.Items.Add(col.Name);
                        ColumnListViewItem.SubItems.Add(col.DataType.Name);
                        ColumnListViewItem.SubItems.Add(col.DataType.
                            MaximumLength.ToString(
                            System.Globalization.CultureInfo.InvariantCulture));
                        ColumnListViewItem.SubItems.Add(
                            col.Nullable.ToString());
                        ColumnListViewItem.SubItems.Add(
                            col.InPrimaryKey.ToString());
                    }
                }

                // Now we render the listview
                ColumnsListView.EndUpdate();
            }
            catch (SmoException ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                this.Cursor = csr;  // Restore the original cursor
            }
        }

        private void GenerateXSD()
        {
            SqlDataAdapter daTables;
            DataSet dsTables;

            for (int count = 0; count <= lstTable.Items.Count - 1; count++)
            {
                string tableName = lstTable.Items[count].ToString();
                dsTables = new DataSet();
                daTables = new SqlDataAdapter("Select * From " + tableName, currentServer.ConnectionContext.SqlConnectionObject);
                daTables.FillSchema(dsTables, SchemaType.Source, tableName);
                dsTables.WriteXmlSchema("C://Temp//" + tableName + ".xsd");
            }
            MessageBox.Show("Completed");
        }



        /* used for some aig-hsb proj... refine later if needed.

        private void btnGetAll_Click(object sender, EventArgs e)
        {
            SqlDataAdapter daTables;
            DataSet dsTables;

            dsTables = new DataSet();

            for (int count = 0; count <= lstTable.Items.Count - 1; count++)
            {
                string tableName = lstTable.Items[count].ToString();
                if (tableName.StartsWith("[dbo].[Mod_") || tableName.Equals("[dbo].[RFS]") || tableName.Equals("[dbo].[Survey]"))
                {
                    daTables = new SqlDataAdapter("Select * From " + tableName, SqlServerSelection.ConnectionContext.SqlConnectionObject);
                    daTables.FillSchema(dsTables, SchemaType.Source, tableName);
                }
            }
            dsTables.WriteXmlSchema("D://AllMod.xsd");
            MessageBox.Show("Completed");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                DataSet dsSchema;
                dsSchema = new DataSet();
                dsSchema.ReadXmlSchema("D://Temp//cat.xsd");

                StringBuilder sbText = new StringBuilder();

                for (int count = 0; count <= dsSchema.Tables.Count - 1; count++)
                {
                    sbText.Append("CREATE TABLE " + dsSchema.Tables[count].TableName + "(\n");
                    //MessageBox.Show(dsSchema.Tables[count].TableName);

                    foreach (DataColumn dc in dsSchema.Tables[count].Columns)
                    {
                        sbText.Append("\t[" + dc.ColumnName + "] ");
                        switch (dc.DataType.ToString())
                        {
                            case "System.Int32":
                                sbText.Append("[int] ");
                                break;
                            case "System.String":
                                if (dc.MaxLength > 10)
                                {
                                    sbText.Append("[varchar] (" + dc.MaxLength.ToString() + ")");
                                }
                                else
                                {
                                    sbText.Append("[char] (" + dc.MaxLength.ToString() + ")");
                                }

                                break;
                            case "System.DateTime":
                                sbText.Append("[datetime] ");
                                break;
                            default:
                                sbText.Append("[varchar] (100)");
                                break;
                        }
                        if (dc.AllowDBNull)
                        {
                            sbText.Append("NULL,\n");
                        }
                        else
                        {
                            sbText.Append("NOT NULL,\n");
                        }

                        //MessageBox.Show(dc.ColumnName + " " + dc.DataType.ToString() + " " + dc.MaxLength.ToString() + " " + dc.AllowDBNull.ToString()+ " " + dc.AutoIncrement.ToString()+ " " + dc.ColumnMapping.ToString()+ " " + dc.DefaultValue.ToString()+ " " + dc.Unique.ToString());
                    }
                    sbText.Append(")");
                }
                rtbCode.Clear();
                rtbCode.Text = sbText.ToString();
                MessageBox.Show("Done!");
            }
            catch (Exception ex)
            {
                rtbCode.Clear();
                MessageBox.Show(ex.Message);
            }

        }
        */

    }
}